﻿using Dapper;
using ML.Entity;
using ML.IRepository;
using MySql.Data.MySqlClient;
using System.Linq;

namespace ML.Repository
{
    public class UserRepository : DBFactory, IUserRepository
    {
        public Pager<UserEntity> List(UserSearch userSearch)
        {
            //组装调用存储过程参数
            var criteria = new PageCriteria() { TableName = "ml_user" };
            if (!string.IsNullOrEmpty(userSearch.nickname_search))
            {
                criteria.Conditions += string.Format(" AND NickName LIKE'%{0}%'", userSearch.nickname_search);
            }
            if (!string.IsNullOrEmpty(userSearch.tel_search))
            {
                criteria.Conditions += string.Format(" AND Tel LIKE '%{0}%'", userSearch.tel_search);
            }
            criteria.Conditions += string.Format(" AND DelMark=0");
            criteria.PageIndex = userSearch.pageindex;
            criteria.Field = @"Id,UserName,NickName,Tel,UserType,OpenId,PhotoGraph,Gender,InvitationCode,DelMark,CreateTime";
            criteria.PageSize = userSearch.pagesize;
            criteria.SortName = userSearch.ordername;
            if (userSearch.orderby)
            {
                criteria.Sort = "DESC";
            }
            else
            {
                criteria.Sort = "ASC";
            }

            //调用存储过程进行查询并且返回
            var list = GetPageData<UserEntity>(criteria);
            return list;
        }

        public UserEntity Login(string login_name_in, string user_pwd_in)
        {
            const string sql = @"SELECT Id,UserName,NickName,Tel,OpenId,UserType,PhotoGraph FROM ml_user WHERE DelMark=0 AND NickName=@NickName AND `Password`=@Password";
            using (_db)
            {
                return _db.Query<UserEntity>(sql, new { @NickName = login_name_in, @Password = user_pwd_in }).FirstOrDefault();
            }
        }

        public UserEntity GetById(string id)
        {
            const string sql = @"SELECT * FROM ml_user Where Id=@Id";
            using (_db)
            {
                return _db.Query<UserEntity>(sql, new { @Id = id }).FirstOrDefault();
            }
        }

        public UserEntity GetByOpenId(string openid)
        {
            const string sql = @"SELECT * FROM ml_user Where OpenId=@OpenId";
            using (_db)
            {
                return _db.Query<UserEntity>(sql, new { @OpenId = openid }).FirstOrDefault();
            }
        }

        public UserEntity getByInvitationCode(string invitationcode)
        {
            const string sql = @"SELECT * FROM ml_user Where InvitationCode=@InvitationCode";
            using (_db)
            {
                return _db.Query<UserEntity>(sql, new { @InvitationCode = invitationcode }).FirstOrDefault();
            }
        }

        public bool Add(UserEntity entity)
        {
            const string sql = @"INSERT INTO ml_user(Id,UserName,NickName,Tel,Password,UserType,OpenId,PhotoGraph,
                Gender,Country,City,Province,InvitationCode,DelMark,CreateTime) 
            VALUES (@Id,@UserName,@NickName,@Tel,@Password,@UserType,@OpenId,@PhotoGraph,@Gender,@Country,@City,@Province,
                @InvitationCode,@DelMark,@CreateTime);";
            using (_db)
            {
                if (_db.Execute(sql, entity) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public bool AddUser(UserEntity userEntity, UserInvitationEntity userInvitationEntity)
        {
            //两个数据库添加的Sql语句
            const string user_sql = @"INSERT INTO ml_user(Id,UserName,NickName,Tel,Password,UserType,OpenId,PhotoGraph,
                Gender,Country,City,Province,InvitationCode,DelMark,CreateTime) 
            VALUES (@Id,@UserName,@NickName,@Tel,@Password,@UserType,@OpenId,@PhotoGraph,@Gender,@Country,@City,@Province,
                @InvitationCode,@DelMark,@CreateTime);";
            const string userinvitation_sql = @"INSERT INTO ml_userinvitation(Id,InvitationId,BeInvitationId,AddType,ContentSource,DelMark) 
                VALUES(@Id,@InvitationId,@BeInvitationId,@AddType,@ContentSource,@DelMark)";

            //构造事务、要么执行成功要么执行失败
            using (var conn = OpenConnection())
            {
                MySqlTransaction trans = conn.BeginTransaction();
                var isUserTrue = conn.Execute(user_sql, userEntity, trans);
                if (isUserTrue > 0)
                {
                    var isuserInvitationTrue = conn.Execute(userinvitation_sql, userInvitationEntity, trans);
                    if (isuserInvitationTrue < 0)
                    {
                        trans.Rollback();
                        return false;
                    }
                    trans.Commit();
                }
                else
                {
                    trans.Rollback();
                    return false;
                }
                return true;
            }
        }

        public bool Update(UserEntity entity)
        {
            const string sql = @"UPDATE ml_user Set UserName=@UserName,NickName=@NickName,Tel=@Tel,Password=@Password,
                UserType=@UserType,OpenId=@OpenId,PhotoGraph=@PhotoGraph,InvitationCode=@InvitationCode,DelMark=@DelMark WHERE Id=@Id";
            using (_db)
            {
                if (_db.Execute(sql, entity) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public bool UpdateUser(UserEntity entity)
        {
            const string sql = @"UPDATE ml_user Set UserName=@UserName,NickName=@NickName,Tel=@Tel,PhotoGraph=@PhotoGraph WHERE Id=@Id";
            using (_db)
            {
                if (_db.Execute(sql, entity) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public bool Delete(UserEntity entity)
        {
            throw new System.NotImplementedException();
        }

        public bool Delete(string id)
        {
            const string sql = @"DELETE FROM ml_user WHERE Id=@Id";
            using (_db)
            {
                if (_db.Execute(sql, new { @Id = id }) > 0)
                {
                    return true;
                }
                return false;
            }
        }
    }
}